"""
Case Type   : 事务-ustore
Case Name   : 事务->Alter Table->子事务->Alter Table->主回滚->kill数据库
Create At   : 2022/1/14
Owner       : @kyleze
Description :
    1.创建表
    2.Alter Table
    # alter table {self.schema}.{self.table1} ADD address varchar(30);
    # alter table {self.schema}.{self.table1} drop COLUMN address;
    3.kill数据库
    4.查询数据
    5.清理环境
Expect      :
    1.成功
    2.成功
    3.成功
    4.查询到数据
    5.成功
History     :
"""
import os
import sys
import unittest

from yat.test import macro
from yat.test import Node

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class USTORE(unittest.TestCase):
    def setUp(self):
        self.pri_user = Node('PrimaryDbUser')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.log = Logger()
        self.schema = 'schema_ustore_trans'
        self.table1 = 't_ustore_trans_0004_01'
        self.table2 = 't_ustore_trans_0004_02'
        text = f'---{os.path.basename(__file__)} start---'
        self.log.info(text)
    
    def testunit_1(self):
        text = '----step1:创建表c;expect:成功----'
        self.log.info(text)
        q_str = f'''drop table if exists {self.schema}.{self.table1} cascade;
            drop table if exists {self.schema}.{self.table2} cascade;
            create table {self.schema}.{self.table1}(c_id int,c_d_id int not null,
            c_w_id int not null,c_first varchar(16) not null,c_middle char(22),c_last varchar(16) not null,c_street_1 varchar(20) not null,c_street_2 varchar(20),c_city varchar(20) not null,
            c_state char(22) not null,c_zip char(9) not null,c_phone char(16) not null,
            c_since timestamp,c_credit char(22) not null,c_credit_lim numeric(22,12),c_discount
            numeric(4,4),c_balance numeric(22,12),c_ytd_payment real not null,c_payment_cnt number not null,c_delivery_cnt bool not null,c_end date not null,c_data clob,c_clob clob) with (storage_type=ustore);
            insert into {self.schema}.{self.table1} select * from {self.schema}.t_ustore_trans_0001;
            create unique index {self.schema}.index_trans_0004_1 on 
            {self.schema}.{self.table1}(c_id);
            create index {self.schema}.index_trans_0004_2 on 
            {self.schema}.{self.table1}(c_first);
            create index {self.schema}.index_trans_0004_3 on 
            {self.schema}.{self.table1}(c_first,c_middle,c_last);
            create index {self.schema}.index_trans_0004_4 on 
            {self.schema}.{self.table1}(c_first,c_id);
            create table {self.schema}.{self.table2}(c_id int,c_d_id int not null,c_w_id int not null,
            c_first varchar(16) not null,c_middle char(22),c_last varchar(16) not null,c_street_1 varchar(20)
            not null,c_street_2 varchar(20),c_city varchar(20) not null,c_state char(22) not null,c_zip char(9) not null,c_phone char(16) not null,c_since timestamp,c_credit char(22) not null,c_credit_lim
            numeric(22,12),c_discount numeric(4,4),c_balance numeric(22,12),c_ytd_payment real not null,
            c_payment_cnt number not null,c_delivery_cnt bool not null,c_end date not null,c_data clob,
            c_clob clob) with (storage_type=ustore);
            '''
        self.log.info(q_str)
        q_res = self.pri_sh.execut_db_sql(q_str)
        self.log.info(q_res)
        self.assertNotIn('ERROR', q_res, '执行失败: ' + text)
        self.assertIn(self.constant.CREATE_INDEX_SUCCESS_MSG, q_res,
              '执行失败: ' + text)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, q_res,
                      '执行失败: ' + text)

        text = '----step2:Alter Table;expect:成功----'
        self.log.info(text)
        q_str = f'''start transaction;
        alter table {self.schema}.{self.table1} ADD address varchar(30);
        savepoint point0004;
        alter table {self.schema}.{self.table1} drop column address;
        rollback;
        '''
        self.log.info(q_str)
        q_res = self.pri_sh.execut_db_sql(q_str)
        self.log.info(q_res)
        self.assertNotIn('ERROR', q_res, '执行失败: ' + text)
        self.assertIn('ROLLBACK', q_res, '执行失败: ' + text)
        self.assertIn(self.constant.ALTER_TABLE_MSG, q_res,'执行失败: ' + text)

        text = '----step3:kill-重启数据库;expect:成功----'
        self.log.info(text)
        result = self.pri_sh.restart_db_cluster()
        self.log.info(result)
        status = self.pri_sh.get_db_cluster_status()
        self.assertTrue('Degraded' in status or 'Normal' in status)

        text = '----step4:查询数据;expect:有数据----'
        self.log.info(text)
        q_str = f'''
            select * from {self.schema}.{self.table1} where c_id=1;
            '''
        self.log.info(q_str)
        q_res = self.pri_sh.execut_db_sql(q_str)
        self.log.info(q_res)
        self.assertNotIn('ERROR', q_res, '执行失败: ' + text)
        self.assertIn('1 row', q_res, '执行失败: ' + text)
        self.assertIn('is1cmRDs1', q_res, '执行失败: ' + text)

    def tearDown(self):
        text = '----step5:清理环境;expect:成功----'
        self.log.info(text)
        sql = f'drop table {self.schema}.{self.table1},' \
              f'{self.schema}.{self.table2} cascade;'
        result = self.pri_sh.execut_db_sql(sql)
        self.log.info(result)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, result,
                      '执行失败: ' + text)
        text = f'---{os.path.basename(__file__)} end---'
        self.log.info(text)
